# -*- coding:utf-8 -*-
#Python
#Generator: PyCharm
#Author: Zhong
import os

os.environ['DJANGO_SETTINGS_MODULE'] = 'settings'

import sys

sys.path.pop(0)
sys.path.insert(0, 'f:\\emps\\emps')

import xlrd

from emp.models import Department, Employee, Category
from django.db import transaction

def toInt(str):
    if isinstance(str, float):
        return int(str)
    str = str.strip()
    if not str:
        return None
    return int(str)

@transaction.commit_on_success
def readToTable(filePath):
    bk = xlrd.open_workbook(filePath)
    sh = bk.sheet_by_name("Sheet1")
    startRow = 3
    rowCount = sh.nrows
    rowNum = 0
    colCount = sh.ncols
    print "rowCount %d, colCount %d" % (rowCount, colCount)

    deps = {}
    for i in range(startRow, rowCount):
        row_data = sh.row_values(i)
        dep = row_data[1].strip()
        row_data[1] = dep

        if len(row_data) != colCount:
            raise Exception('column number error!')

        if deps.get(dep) is None:
            rowNum += 1
            Department.objects.get_or_create(name=dep, order=rowNum)
            deps.setdefault(dep, [row_data])
        else:
            deps.get(dep).append(row_data)

    for dep, emps in deps.iteritems():
        for arr in emps:
            employee = Employee(order=toInt(arr[0]), post=arr[2].strip(), name=arr[3].strip(),
                                inner=toInt(arr[4]),
                                outer=toInt(arr[5]), mobile=toInt(arr[6]),
                                short_tel=toInt(arr[7]), telephone=toInt(arr[8]), remark=arr[10].strip())
            employee.save()
            employee.department = [Department.objects.get(name=dep)]

        print dep, len(emps)

#readToTable(u"C:\\通讯录（总表）.xls")

def oldDep():
    import sqlite3

    conn = sqlite3.connect(u'F:\\emps\\emps\\db2.db')
    c = conn.cursor()
    rec = c.execute('''select * from emp_category''')
    for cate in rec.fetchall():
        print Category(name=cate[1], order=cate[0]).save()


oldDep()







